--EXEC sp_create_update_all_inventory_transit_lot 40463,300,'ATK','FG','Admin','CG6225AAT','ATKLot3B','ATKLot3', 'ATK','0944','Running','',200,'Tape & Reel'
ALTER PROCEDURE sp_create_update_all_inventory_transit_lot
@LOT_ID				INT,
@SHIP_WAFER_QTY		INT,
@SHIP_DIE_QTY		INT,
@NEXT_LOCATION     VARCHAR(50),
@NEXT_PROCESS      VARCHAR(50),
@USER_NAME			VARCHAR(50),
@PART_NUMBER		VARCHAR(50),
@LOT_NUMBER			VARCHAR(50),
@FAB_LOTNO			VARCHAR(50),
@VENDOR_NAME		VARCHAR(50),
@CHECKED_WAFERIDS VARCHAR(100),
@UNCHECKED_WAFERIDS VARCHAR(100),
@REMAIN_WAFER_QTY	INT,
@LOT_STATUS			VARCHAR(50),
@LOT_COMMENTS	    VARCHAR(500),
@CURRENT_PROCESS    VARCHAR(50),
@SPLITLot1_NO		VARCHAr(50),
@SPLITLot2_NO		VARCHAr(50),
@SPLITLot1_QTy		INT,
@SPLITLot2_QTY		INT
AS 
BEGIN

	--NeED TO Implement BULK QTY IN
	DECLARE @SHIPPEDLOTID   INT
	DECLARE @DATE_OUT	DATETIME
	DECLARE @DATE_IN	DATETIME
	DECLARE @NEW_LOT_ID INT
	DECLARE @SPLIT1_LOT_ID INT
	DECLARE @SPLIT2_LOT_ID INT
	DECLARE @PROCESS_STEP_ID INT
	DECLARE @LOCATION_ID INT
	DECLARE @RECEIVE_VENDOR VARCHAR(50)
	DECLARE @LOT_CURRENT_STATUS VARCHAR(50)
	DECLARE @QTY_IN_QTY INT

	DECLARE @LOT_PROCESSSTEP_ID INT
	DECLARE @LOT_LOCATION_ID INT
	
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @NEXT_PS_TYPE_NAME	VARCHAR(50)
	DECLARE @LOT_CATEGORY	 VARCHAR(50)
	declare @shippedlotCount int
	DECLARE @QTY_IN INT
	
	SET @DATE_OUT = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)

	SELECT @PS_TYPE_NAME = Base_Process_Type FROM PROCESS_TYPES_TABLE WHERE Process_TYPE = @CURRENT_PROCESS
	SELECT @NEXT_PS_TYPE_NAME = Base_Process_Type FROM PROCESS_TYPES_TABLE WHERE Process_TYPE = @NEXT_PROCESS	

	Select @LOT_PROCESSSTEP_ID = PROCESSSTEP_ID, @LOT_LOCATION_ID =LOCATION_ID, @LOT_CATEGORY = LOT_CATEGORY, @DATE_IN = Date_IN  FROM LOT_Table where Lot_id = @LOT_ID

	IF @LOT_STATUS = 'HOLD'	
	BEGIN
			UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Hold', LOT_COMMENTS = @LOT_COMMENTS,  USER_NAME = @USER_NAME WHERE LOT_ID = @LOT_ID		
			RETURN
	END

	DECLARE @GENERATE_SHIP_LOT_BY_WAFER_ID VARCHAR(5)
	DECLARE @WAFERIDS_AS_COMMA VARCHAR(150)
	DECLARE @WAFER_NO		   VARCHAR(5)	
	DECLARE @WAFER_IDS_TABLE   TABLE ( WAFER_ID INT )
	DECLARE @LOTCURSOR CURSOR

	SET @WAFERIDS_AS_COMMA = REPLACE(@CHECKED_WAFERIDS, ';',',')
	INSERT INTO @WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @WAFERIDS_AS_COMMA  )
	SELECT @GENERATE_SHIP_LOT_BY_WAFER_ID = ISNULL(PROPERTY_VALUE,'FALSE') FROM CONFIGURATION_TABLE WHERE PROPERTY_KEY = 'scalar.chiptrack.operations.shippedLot.showShippedLotByWafer'

	IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT'  
	BEGIN
		--INSERTING SHIPPiNG RECORED
		IF @GENERATE_SHIP_LOT_BY_WAFER_ID = 'TRUE'
		BEGIN
			SET @LOTCURSOR = CURSOR FOR SELECT WAFER_ID FROM @WAFER_IDS_TABLE
			OPEN @LOTCURSOR
			FETCH NEXT FROM @LOTCURSOR INTO @WAFER_NO
			WHILE @@FETCH_STATUS = 0
			BEGIN
				INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, STATUS, SHIP_QTY, WAFER_ID, TIMESTAMP, SHIP_STATUS, SL_NEXTLOCATION, SL_NEXT_PROCESS ) 
				VALUES( @LOT_ID, @DATE_OUT, 'TRANSIT', 1,@WAFER_NO, CURRENT_TIMESTAMP, 0, @NEXT_LOCATION, @NEXT_PROCESS )
				SET @WAFER_NO = NULL
				FETCH NEXT FROM @LOTCURSOR INTO @WAFER_NO
			END
			CLOSE @LOTCURSOR
			DEALLOCATE @LOTCURSOR	
			DELETE FROM @WAFER_IDS_TABLE
		END
		ELSE
		BEGIN
			INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, STATUS, SHIP_QTY, WAFER_ID, TIMESTAMP, SHIP_STATUS, SL_NEXTLOCATION, SL_NEXT_PROCESS ) 
				VALUES( @LOT_ID, @DATE_OUT, 'TRANSIT', @SHIP_WAFER_QTY,@CHECKED_WAFERIDS, CURRENT_TIMESTAMP, 0, @NEXT_LOCATION, @NEXT_PROCESS )
			SET @SHIPPEDLOTID = ( SELECT @@IDENTITY )
		END

		IF @REMAIN_WAFER_QTY > 0
		BEGIN
			UPDATE LOT_TABLE SET WAFER_QTY_IN = WAFER_QTY_IN - @SHIP_WAFER_QTY, LOT_DROP_OUT = 0 ,LOT_COMMENTS = @LOT_COMMENTS, USER_NAME = @USER_NAME WHERE LOT_ID = @LOT_ID
			IF @PS_TYPE_NAME = 'FAB'
			BEGIN
				UPDATE FAB_LOT SET WAFER_ID = @UNCHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
			END
			ELSE IF @PS_TYPE_NAME = 'SORT'
			BEGIN
				UPDATE SORT_LOT SET SL_WAFER_ID = @UNCHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
			END
			ELSE IF @PS_TYPE_NAME = 'Wafer Store'
			BEGIN
				UPDATE INVENTORY_LOT SET WAFER_ID = @UNCHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
			END
		END
		ELSE
		BEGIN
			UPDATE LOT_TABLE SET WAFER_QTY_OUT = @SHIP_WAFER_QTY , DATE_OUT = @DATE_OUT, LOT_CURRENT_STATUS = 'Close', LOT_COMMENTS = @LOT_COMMENTS, USER_NAME = @USER_NAME WHERE LOT_ID = @LOT_ID
		END
	END
	ELSE
	BEGIN
		IF @SPLITLot1_QTy > 0 AND @SPLITLot2_QTy > 0
		BEGIN
			UPDATE LOT_TABLE SET LOT_SPLIT = 0, LOT_CURRENT_STATUS = 'Close', LOT_COMMENTS = @LOT_COMMENTS, USER_NAME = @USER_NAME WHERE LOT_ID = @LOT_ID

			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, qty_in,  Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, LOT_DROP_OUT, USER_NAME) 
				values (@LOT_PROCESSSTEP_ID, @LOT_LOCATION_ID, @SPLITLot1_NO, @FAB_LOTNO, @SPLITLot1_NO,  @SPLITLot1_QTy,  @DATE_IN, 'Close', CURRENT_TIMESTAMP, 0, @LOT_CATEGORY, 0 , @USER_NAME )
				select @SPLIT1_LOT_ID = @@IDENTITY

			INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, STATUS, SHIP_QTY, TIMESTAMP, SHIP_STATUS, SL_NEXTLOCATION, SL_NEXT_PROCESS ) 
				VALUES( @SPLIT1_LOT_ID, @DATE_OUT, 'TRANSIT', @SPLITLot1_QTy, CURRENT_TIMESTAMP, 0, @NEXT_LOCATION, @NEXT_PROCESS )
			SET @SHIPPEDLOTID = ( SELECT @@IDENTITY )
			 
			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, qty_in,  Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, LOT_DROP_OUT, USER_NAME) 
				values (@LOT_PROCESSSTEP_ID, @LOT_LOCATION_ID, @SPLITLot2_NO, @FAB_LOTNO, @SPLITLot2_NO,  @SPLITLot2_QTy,  @DATE_IN, 'Running', CURRENT_TIMESTAMP, 0, @LOT_CATEGORY, 0 , @USER_NAME )			
			SELECT @SPLIT2_LOT_ID = @@IDENTITY

			IF @PS_TYPE_NAME = 'Die Bank' OR @PS_TYPE_NAME = 'Class Store' OR @PS_TYPE_NAME = 'Finished Goods'
			BEGIN
				insert into Inventory_Lot( Lot_ID, Timestamp ) values (@SPLIT1_LOT_ID, CURRENT_TIMESTAMP)		
				insert into Inventory_Lot( Lot_ID, Timestamp ) values (@SPLIT2_LOT_ID, CURRENT_TIMESTAMP)
			END
			ELSE IF @PS_TYPE_NAME = 'Assembly' 
			BEGIN
				insert into Assembly_Lot( Lot_ID, Timestamp ) values (@SPLIT1_LOT_ID, CURRENT_TIMESTAMP)		
				insert into Assembly_Lot( Lot_ID, Timestamp ) values (@SPLIT2_LOT_ID, CURRENT_TIMESTAMP)	
			END
			ELSE IF @PS_TYPE_NAME = 'TEST' 
			BEGIN
				insert into Final_Test_Lot( Lot_ID, Timestamp ) values (@SPLIT1_LOT_ID, CURRENT_TIMESTAMP)		
				insert into Final_Test_Lot( Lot_ID, Timestamp ) values (@SPLIT2_LOT_ID, CURRENT_TIMESTAMP)
			END

			SET @LOT_NUMBER = @SPLITLot1_NO
			SET @SHIP_DIE_QTY = @SPLITLot1_QTy

			INSERT INTO Lot_Split_Table (Parent_Lot_Id, Child_Lot_Id1,Child_Lot_Id2, User_Name, TIMESTAMP )
				VALUES (@LOT_ID, @SPLIT1_LOT_ID, @SPLIT2_LOT_ID, @USER_NAME, @DATE_OUT )
	
		END
		ELSE
		BEGIN
			IF @PS_TYPE_NAME = 'Die Bank'
			BEGIN
				select @shippedlotCount = count(ShippedLot_ID) from Shipped_Lot where Lot_ID = @lot_id and Status = 'Close' and Ship_Status > 0 and Ship_Date = @DATE_OUT 
				IF( @shippedlotCount = 0 )
				BEGIN	
				
					select @SHIPPEDLOTID = ShippedLot_ID from Shipped_Lot where Lot_ID = @lot_id and status = 'TRANSIT' and Ship_Status = 0 and Ship_Date = @DATE_OUT 
					--if @shippedLotID is null 
	 				IF ( @@rowcount = 0 )
	 				BEGIN	
						INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, STATUS, SHIP_QTY, TIMESTAMP, SHIP_STATUS, SL_NEXTLOCATION , SL_NEXT_PROCESS ) VALUES 
							( @LOT_ID, @DATE_OUT, 'TRANSIT', @SHIP_DIE_QTY, CURRENT_TIMESTAMP, 0, @NEXT_LOCATION , @NEXT_PROCESS )
				
						SET @SHIPPEDLOTID = ( SELECT @@IDENTITY )
					END
					
					UPDATE LOT_TABLE SET QTY_IN = QTY_IN - @SHIP_DIE_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @LOT_ID
					
					SELECT @QTY_IN = ISNULL(QTY_IN, 0) FROM LOT_TABLE WHERE LOT_ID = @LOT_ID

					IF @QTY_IN = 0
					BEGIN
						UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @LOT_ID
					END
				END
			END
			ELSE
			BEGIN
				INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, STATUS, SHIP_QTY, TIMESTAMP, SHIP_STATUS, SL_NEXTLOCATION, SL_NEXT_PROCESS ) 
					VALUES( @LOT_ID, @DATE_OUT, 'TRANSIT', @SHIP_DIE_QTY, CURRENT_TIMESTAMP, 0, @NEXT_LOCATION, @NEXT_PROCESS )
				SET @SHIPPEDLOTID = ( SELECT @@IDENTITY )
				UPDATE LOT_TABLE SET QTY_OUT = @SHIP_DIE_QTY , DATE_OUT = @DATE_OUT, LOT_CURRENT_STATUS = 'Close', LOT_COMMENTS = @LOT_COMMENTS, USER_NAME = @USER_NAME WHERE LOT_ID = @LOT_ID
			END
		END
	END
				
	
	--WHEN USER TRANSFER FROM SAME VANDOR TO SAME VENDOR
	IF @VENDOR_NAME = @NEXT_LOCATION
	BEGIN
		
		SELECT @PROCESS_STEP_ID = PROCESS_STEP_ID FROM FN_GET_PROCESS_STEP_ID(@PART_NUMBER,@NEXT_PS_TYPE_NAME,@NEXT_PROCESS )

		IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
		BEGIN
			SELECT TOp 1 @PROCESS_STEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @LOT_PROCESSSTEP_ID, 1 ) WHERE PS_TYPE = @NEXT_PROCESS
			IF @PROCESS_STEP_ID IS NULL
			BEGIN	-- DUMMY UPDATE TO AVOID SQLEXCEPTION
				UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
				RETURN
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
			END
		END	
		
		SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @NEXT_PS_TYPE_NAME AND L_VENDOR_NAME = @VENDOR_NAME
		IF @GENERATE_SHIP_LOT_BY_WAFER_ID = 'TRUE'
		BEGIN
			INSERT INTO @WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @WAFERIDS_AS_COMMA  )

			SET @LOTCURSOR = CURSOR FOR SELECT WAFER_ID FROM @WAFER_IDS_TABLE
			OPEN @LOTCURSOR
			FETCH NEXT FROM @LOTCURSOR INTO @WAFER_NO
			WHILE @@FETCH_STATUS = 0
			BEGIN
					SELECT @SHIPPEDLOTID = SHIPPEDLOT_ID FROM SHIPPED_LOT WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_NO
					UPDATE SHIPPED_LOT SET STATUS = 'Close', SHIP_STATUS = 1, Close_Date = CURRENT_TIMESTAMP, Closed_By = @USER_NAME WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID AND WAFER_ID = @WAFER_NO
				SET @WAFER_NO = NULL
				SET @SHIPPEDLOTID = NULL
				FETCH NEXT FROM @LOTCURSOR INTO @WAFER_NO
			END
			CLOSE @LOTCURSOR
			DEALLOCATE @LOTCURSOR	
			DELETE FROM @WAFER_IDS_TABLE
		END
		ELSE
		BEGIN
			UPDATE SHIPPED_LOT SET STATUS = 'Close', SHIP_STATUS = 1, Close_Date = CURRENT_TIMESTAMP, Closed_By = @USER_NAME WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID 
		END
	
		SELECT @NEW_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER 
					
		IF @NEW_LOT_ID IS NULL
		BEGIN
			IF @NEXT_PS_TYPE_NAME = 'FAB' OR @NEXT_PS_TYPE_NAME = 'WAFER STORE' OR @NEXT_PS_TYPE_NAME = 'SORT' 
			BEGIN	
				insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_IN,  Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, LOT_DROP_OUT, USER_NAME) 
					values (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @FAB_LOTNO, @LOT_NUMBER,  @SHIP_WAFER_QTY,  @DATE_OUT, 'Running', CURRENT_TIMESTAMP, 0, 'PROD', 0 , @USER_NAME )
				select @NEW_LOT_ID = @@IDENTITY
			END
			ELSE IF  @NEXT_PS_TYPE_NAME = 'Die Bank' 
			BEGIN
					--NEED TO IMPLEMENT Here
					insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, qty_in,  Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, LOT_DROP_OUT, USER_NAME) 
					values (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @FAB_LOTNO, @LOT_NUMBER,  @SHIP_DIE_QTY,  @DATE_OUT, 'Running', CURRENT_TIMESTAMP, 0, 'PROD', 0 , @USER_NAME )
				select @NEW_LOT_ID = @@IDENTITY
			END
			ELSE
			BEGIN
				insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, qty_in,  Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, LOT_DROP_OUT, USER_NAME) 
					values (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @FAB_LOTNO, @LOT_NUMBER,  @SHIP_DIE_QTY,  @DATE_OUT, 'Running', CURRENT_TIMESTAMP, 0, 'PROD', 0 , @USER_NAME )
				select @NEW_LOT_ID = @@IDENTITY
			END
							
			IF @NEXT_PS_TYPE_NAME = 'FAB'
			BEGIN
				INSERT INTO FAB_LOT (LOT_ID, Wafer_Id, Timestamp) VALUES ( @NEW_LOT_ID, @CHECKED_WAFERIDS, CURRENT_TIMESTAMP )  
			END
			ELSE IF @NEXT_PS_TYPE_NAME = 'SORT'
			BEGIN
				INSERT INTO SORT_LOT (LOT_ID, Sl_Wafer_ID, Timestamp) VALUES ( @NEW_LOT_ID, @CHECKED_WAFERIDS, CURRENT_TIMESTAMP )  
			END
			ELSE IF @NEXT_PS_TYPE_NAME = 'Wafer Store' OR @NEXT_PS_TYPE_NAME = 'Die Bank' OR @NEXT_PS_TYPE_NAME = 'Class Store' OR @NEXT_PS_TYPE_NAME = 'Finished Goods'
			BEGIN
				insert into Inventory_Lot( Lot_ID, Wafer_id, Timestamp ) values (@NEW_LOT_ID, @CHECKED_WAFERIDS, CURRENT_TIMESTAMP)		
			END
			ELSE IF @NEXT_PS_TYPE_NAME = 'Assembly' 
			BEGIN
				--select * from Assembly_Lot
				insert into Assembly_Lot( Lot_ID, Timestamp ) values (@NEW_LOT_ID, CURRENT_TIMESTAMP)		
			END
			ELSE IF @NEXT_PS_TYPE_NAME = 'TEST' 
			BEGIN
				--select * from Final_Test_Lot
				insert into Final_Test_Lot( Lot_ID, Timestamp ) values (@NEW_LOT_ID, CURRENT_TIMESTAMP)		
			END
		END
		ELSE --WHEN NEW LOT_ID IS NOT NULL
		BEGIN
			SELECT @LOT_CURRENT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @NEW_LOT_ID
			IF @LOT_CURRENT_STATUS <> 'Close'
			BEGIN
				IF @NEXT_PS_TYPE_NAME = 'FAB' OR @NEXT_PS_TYPE_NAME = 'WAFER STORE' OR @NEXT_PS_TYPE_NAME = 'SORT' 
				BEGIN
					UPDATE LOT_TABLE SET WAFER_QTY_IN = WAFER_QTY_IN + @SHIP_WAFER_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_LOT_ID
				END
				ELSE
				BEGIN
					UPDATE LOT_TABLE SET QTY_IN = QTY_IN + @SHIP_DIE_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_LOT_ID
				END
			END
			ELSE
			BEGIN
				IF @NEXT_PS_TYPE_NAME = 'FAB' OR @NEXT_PS_TYPE_NAME = 'WAFER STORE' OR @NEXT_PS_TYPE_NAME = 'SORT' 
				BEGIN
					UPDATE LOT_TABLE SET Lot_Current_Status = 'Running', WAFER_QTY_IN = @SHIP_WAFER_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_LOT_ID
				END
				ELSE
				BEGIN
					UPDATE LOT_TABLE SET Lot_Current_Status = 'Running', QTY_IN = @SHIP_DIE_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_LOT_ID
				END
			END

			IF @NEXT_PS_TYPE_NAME = 'FAB'
			BEGIN
				IF @LOT_STATUS <> 'Close' 
				BEGIN
					UPDATE FAB_LOT SET WAFER_ID = ISNULL(WAFER_ID,'') + ';' + @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
				END
				ELSE
				BEGIN
					UPDATE FAB_LOT SET WAFER_ID = @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
				END
			END
			ELSE IF @NEXT_PS_TYPE_NAME = 'SORT'
			BEGIN
				IF @LOT_STATUS <> 'Close' 
				BEGIN
					UPDATE SORT_LOT SET SL_WAFER_ID = ISNULL(SL_WAFER_ID,'') + ';' + @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
				END
				ELSE
				BEGIN
					UPDATE SORT_LOT SET SL_WAFER_ID = @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
				END
			END
			ELSE IF @NEXT_PS_TYPE_NAME = 'Wafer Store' OR @NEXT_PS_TYPE_NAME = 'Die Bank' OR @NEXT_PS_TYPE_NAME = 'Class Store' OR @NEXT_PS_TYPE_NAME = 'Finished Goods'
			BEGIN
				IF @LOT_STATUS <> 'Close' 
				BEGIN
					UPDATE INVENTORY_LOT SET WAFER_ID = ISNULL(WAFER_ID,'') + ';' + @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
				END
				ELSE
				BEGIN
					UPDATE INVENTORY_LOT SET WAFER_ID = @CHECKED_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
				END
			END
		END--END OF ELSE
	END
END